package cn.chainco.cts.idaoImpl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.orm.hibernate3.HibernateCallback;

import cn.chainco.cts.idao.ICompanyDao;
import cn.chainco.cts.jdo.Company;
import cn.chainco.cts.jdo.Employee;
import cn.chainco.cts.jdo.Tag;
import cn.chainco.cts.jdo.User;
import cn.chainco.cts.jdo.Work;

public class CompanyDaoImpl extends GenericHibernateDaoImpl<Company, Long> implements ICompanyDao{

	@SuppressWarnings("unchecked")
	public Company findCompanyByPrefix(final String prefix){
		List<Company> list = this.getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session _session) throws HibernateException, SQLException{
				Query query = _session.createQuery("from Company c where c.prefix = ? ");
				query.setParameter(0, prefix);
				return query.list();
			}
		});

		if(list.size() > 0){
			return list.get(0);
		}else{
			return null;
		}

	}

	@SuppressWarnings("unchecked")
	public Company findCompayByUser(final User user){
		List<Company> list = this.getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session _session) throws HibernateException, SQLException{
				Query query = _session.createQuery("from Company c where c.user.id = ? ");
				query.setParameter(0, user.getId());
				return query.list();
			}
		});

		if(list.size() > 0){
			return list.get(0);
		}else{
			return null;
		}

	}

	// 标签
	@SuppressWarnings("unchecked")
	public List<Tag> findTagByCompany(Company company, Integer status){
		String sql = " select * from cts_tag where 1=1 and companyId=" + company.getId();
		if(status != null){
			sql += " and status=" + status;
		}

		List<Tag> taglist = this.getSession().createSQLQuery(sql).addEntity(Tag.class).list();
		return taglist;
	}

	// 标签
	public List<Tag> findTagByCompany(Company company, String name , String status){
		String sql = " select * from cts_tag where 1=1 and companyId=" + company.getId();
		if(status != null && !status.trim().equals("")){
			sql += " and status=" + status;
		}

		if(name!=null && !name.trim().equals("")){
			sql += " and name like '%"+name+"%'";
		}
		
		List<Tag> taglist = this.getSession().createSQLQuery(sql).addEntity(Tag.class).list();
		return taglist;
	}
	
	public List<Company> findCompanyListByUser(User user){
		return null;
	}

	/**
	 * @param likeName 是否模糊查询名字
	 * @param likePrefix 是否模糊查询前缀
	 * @param createDate 创建日期（可为空）
	 * @param toMax null 1.小于2.大于 (日期)
	 * @param status 公司状态：null , 0申请中,1已审核,2禁用
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Company> findCompany(String likeName, String likePrefix, String createDate, Integer toMax, Integer status){
		String sql = "select * from cts_company where 1=1 ";

		if(likeName != null && !likeName.equals("")){
			sql += " and name like '%" + likeName + "%'";
		}

		if(likePrefix != null && !likePrefix.equals("")){
			sql += " and prefix like '%" + likePrefix + "%'";
		}

		if(createDate != null && !createDate.equals("")){
			if(toMax != null){
				if(toMax == 1){
					sql += " and createTime<='" + createDate + "'";
				}else{
					sql += " and createTime>='" + createDate + "'";
				}
			}
		}

		if(status != null){
			sql += " and status='" + status + "'";
		}

		sql += " order by createTime desc";
		List<Company> list = this.getSession().createSQLQuery(sql).addEntity(Company.class).list();
		return list;
	}

	/**
	 * 删除公司和相关连的数据
	 * @param company
	 * @return
	 */
	public int delCompayByAll(Company company){
		int code = 0;
		try{
			String workmSql = "delete from cts_work where companyId=" + company.getId();
			String dcSql = "delete from cts_doctemplate where companyId=" + company.getId();
			String feeSql = "delete from cts_fee where companyId=" + company.getId();
			String itemSql = "delete from cts_item where companyId=" + company.getId();
			String tagmSql = "delete from cts_tag where companyId=" + company.getId();
			String emplSql = "delete from cts_employee where companyId=" + company.getId();
			String companySql = "delete from cts_company where id=" + company.getId();

            // WorkDoc
			String femplSql = "from Employee where 1=1 and company.id="+company.getId() + " and user="+company.getUser().getId();
			List<Employee> employee = this.findEList(femplSql);
			if(employee!=null){
				for (Employee employee2 : employee) {
					String workItemSql = "delete from cts_workdoc where employeeId="+employee2.getId();
					delexecureDel(workItemSql);
				}
			}
			
			String fWorkSql = "from Work where company.id="+company.getId();
			List<Work> worklist = this.findEList(fWorkSql);
			if(worklist!=null){
                // WorkItem
				for (Work work3 : worklist) {
					String workDocSql = "delete from cts_workitem where workId="+work3.getId();
					delexecureDel(workDocSql);
				}
				
				// WorkFee
				for (Work work3 : worklist) {
					String workFeeSql = "delete from cts_workfee where workId="+work3.getId();
					delexecureDel(workFeeSql);
				}
			}
			//Work
			
			delexecureDel(workmSql);
			delexecureDel(dcSql);
			delexecureDel(feeSql);
			delexecureDel(itemSql);
			delexecureDel(tagmSql);
			delexecureDel(emplSql);
			delexecureDel(companySql);
			
			delexecureDel(workmSql);
			delexecureDel(dcSql);
			delexecureDel(feeSql);
			delexecureDel(itemSql);
			delexecureDel(tagmSql);
			delexecureDel(emplSql);
			delexecureDel(companySql);
			code = 1;
		}catch(RuntimeException e){
			e.printStackTrace();
		}

		return code;
	}

	private int delexecureDel(String sql){
		int result = this.getSession().createSQLQuery(sql).executeUpdate();
		return 0;
	}
	
	@SuppressWarnings("unchecked")
	private <E>List<E> findEList(String sql){
	  List<E> list = (List<E>)this.getSession().createQuery(sql).list();
      return list;
   }
}
